const express = require('express')
const con = require('../modul/db.js')
const router = express.Router()
let db= con.handleDisconnection()

//登录
router.get('/login', (req, res) => {
  let account = req.query.account //账号
  let password = req.query.password //密码
  let sql = "select * from `user` where account = '"+account+"' and `password` = '"+password+"';";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})

//根据用户编号查询root节点
router.get('/rootMeun', (req, res) => {
  let uid = req.query.uid //用户编号
  let sql = "select * from `user` as u "+
  "inner join userrole as ur on ur.uid = u.uid "+
  "inner join role as r on ur.rid=r.rid "+
  "inner join menurole as mr on mr.rid=r.rid "+
  "inner join menu as m on mr.mid=m.mid "+
  "where u.uid = '"+uid+"' and m.pid = '0'";
  db.query({
    sql: sql,
    nestTables:'_'
  },function(err,result){
    res.send(result)
  })
})
//根据root节点查询子节点
router.get('/childrenMeun', (req, res) => {
  let mid = req.query.mid //root菜单编号
  let sql = "select * from `menu` where pid = '"+mid+"';";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})


//新增用户
router.post('/addUser', (req, res) => {
  let sql = "INSERT INTO `user`(account,`password`,`name`,`number`,`gender`,`phone`,`address`,`age`,`time`,`status`) "+ 
  "VALUES ('"+req.body.account+"', '"+req.body.password+"','"+req.body.name+"','"+req.body.number+"','"+req.body.gender+"','"+req.body.phone+"','"+req.body.address+"','"+req.body.age+"', '"+req.body.time+"','"+req.body.status+"')";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
      //获取用户编号
      var uid = result.insertId;
      //获取角色编号
      var rid = req.body.backup;
      //新增用户角色表
      let sql = "INSERT INTO `userrole`(rid,uid) VALUES ('"+rid+"', '"+uid+"')";
      db.query(sql, (err, result) => {
        if (err) {
          console.log("错误信息",err)
        } else {
          res.json({
            code: 200
          })
        }
      })
    }
  })
})

// 删除用户
router.get('/delUser', (req, res) => {
  let uid = req.query.uid;
  let sql = "DELETE FROM `user` WHERE uid = '"+uid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//修改用户
router.post('/updateUser', (req, res) => {
  //获取用户编号
  let uid = req.body.uid;
  //获取角色编号
  let rid = req.body.rid;
  let sql = "UPDATE `user` SET "+
            "account = '"+req.body.account+"',"+
            "password = '"+req.body.password+"',"+
            "name = '"+req.body.name+"',"+
            "number = '"+req.body.number+"',"+
            "gender = '"+req.body.gender+"',"+
            "phone = '"+req.body.phone+"',"+
            "address = '"+req.body.address+"',"+
            "age = '"+req.body.age+"',"+
            "status = '"+req.body.status+"'"+
            "WHERE uid = '"+uid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      if(''!=rid){
        //修改用户角色表
        let sql = "UPDATE `userrole` SET "+
                  "rid = '"+rid+"'"+
                  "WHERE uid = '"+uid+"'";
        db.query(sql, (err, result) => {
          if (err) {
            console.log("错误信息2",err)
          } else {
            res.json({
              code: 200
            })
          }
        })
      }else{
        res.json({
          code: 200
        })
      }
      
    }
  })
})
//查询所有用户
router.get('/queryUser', (req, res) => {
  let sql = "select r.rname as rname,u.* from `user` as u "+
            "inner join userrole as ur on ur.uid = u.uid "+
            "inner join role as r on ur.rid=r.rid;"
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})
//分页查询所有用户
router.get('/queryUserPage', (req, res) => {
    //模糊查询用户名称
    let input = req.query.input;
      //模糊查询用户地址
    let address = req.query.address;
    //模糊查询用户角色
    let option = req.query.option;
      //模糊查询用户状态
    let status = req.query.status;

  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 8;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select r.rname as rname,u.* from `user` as u "+
            "inner join userrole as ur on ur.uid = u.uid "+
            "inner join role as r on ur.rid=r.rid where 1=1 "
            if(input!='' && input!=undefined){
              sql += "and u.name like '%"+input+"%' ";
            }
            if(address!='' && address!=undefined){
              sql += "and u.address like '%"+address+"%' ";
            }
            if(option!='' && option!=undefined){
              sql += "and r.rid = '"+option+"' ";
            }
            if(status!='' && status!=undefined){
              sql += "and u.status = '"+status+"' ";
            }
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = sql+"limit "+currentPage+","+pageSize+"";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(results)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})
//根据ID查询用户
router.get('/queryUserById', (req, res) => {
  let uid = req.query.uid;
  let sql = "select r.rname as rname,r.rid as rid,u.* from `user` as u "+
  "inner join userrole as ur on ur.uid = u.uid "+
  "inner join role as r on ur.rid=r.rid where u.uid = '"+uid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})

//新增角色
router.post('/addRole', (req, res) => {
  let backup = req.body.pid;
  let sql = "INSERT INTO `role`(rname,`desc`) "+ 
  "VALUES ('"+req.body.rname+"', '"+req.body.desc+"')";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
      //判断新增是不是父节点
        res.json({
          code: 200
        })
    }
  })
})
//查询所有角色
router.get('/queryRole', (req, res) => {
  let sql = "select * from `role`;";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})
//分页查询所有角色
router.get('/queryRolePage', (req, res) => {
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 9;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select * from role";
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = "select * from role "+
               "limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})
// 删除角色
router.get('/delRole', (req, res) => {
  let rid = req.query.rid;
  let sql = "DELETE FROM `role` WHERE rid = '"+rid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//修改角色
router.post('/updateRole', (req, res) => {
  //获取角色编号
  let rid = req.body.rid;
  let sql = "UPDATE `role` SET "+
            "rname = '"+req.body.rname+"',"+
            "`desc` = '"+req.body.desc+"' "+
            "WHERE rid = '"+rid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//根据ID查询角色
router.get('/queryRoleById', (req, res) => {
  let rid = req.query.rid;
  let sql = "select * from role "+
  "where rid = '"+rid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//根据角色编号查询出他所有的父菜单
router.get('/queryMenuByRid', (req, res) => {
  let rid = req.query.rid;
  let sql = "select m.mid as mid from menu as m "+
  "inner join menurole as mr on m.mid=mr.mid  "+
  "where mr.rid = '"+rid+"' and  m.pid = '0' ";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//新增角色对应菜单
router.get('/addRoleMenu', (req, res) => {
  let sql = "INSERT INTO `menurole`(rid,mid) "+ 
  "VALUES ('"+req.query.rid+"', '"+req.query.mid+"' )";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
        res.json({
          code: 200
        })
    }
  })
})
// 删除角色对应菜单
router.get('/delRoleMenu', (req, res) => {
  let rid = req.query.rid;
  let sql = "DELETE FROM `menurole` WHERE rid = '"+rid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})

//新增菜单
router.post('/addMenu', (req, res) => {
  let backup = req.body.pid;
  let sql = "INSERT INTO `menu`(mname,pid,url) "+ 
  "VALUES ('"+req.body.mname+"', '"+req.body.pid+"','"+req.body.url+"' )";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
      //判断新增是不是父节点
        res.json({
          code: 200
        })
    }
  })
})
// 删除菜单
router.get('/delMenu', (req, res) => {
  let mid = req.query.mid;
  let sql = "DELETE FROM `menu` WHERE mid = '"+mid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//修改菜单
router.post('/updateMenu', (req, res) => {
  //获取菜单编号
  let mid = req.body.mid;
  let sql = "UPDATE `menu` SET "+
            "mname = '"+req.body.mname+"',"+
            "url = '"+req.body.url+"' "+
            "WHERE mid = '"+mid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//查询所有父级菜单
router.get('/queryMenu', (req, res) => {
  let sql = "select * from `menu` where pid ='0'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})
//根据ID查询菜单
router.get('/queryMenuById', (req, res) => {
  let mid = req.query.mid;
  let sql = "select * from menu "+
  "where mid = '"+mid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//分页查询所有菜单
router.get('/queryMenuPage', (req, res) => {
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 9;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select * from menu";
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = "select * from menu "+
               "limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})

//新增项目
router.post('/addProject', (req, res) => {
  let sql = "INSERT INTO `project`(pname,`sage`,`sgender`,`type`,`department`) "+ 
  "VALUES ('"+req.body.pname+"', '"+req.body.sage+"','"+req.body.sgender+"','"+req.body.type+"','"+req.body.department+"')";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
// 删除项目
router.get('/delProject', (req, res) => {
  let pid = req.query.pid;
  let sql = "DELETE FROM `project` WHERE pid = '"+pid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//修改项目
router.post('/updateProject', (req, res) => {
  //获取项目编号
  let pid = req.body.pid;
  let sql = "UPDATE `project` SET "+
            "pname = '"+req.body.pname+"',"+
            "sage = '"+req.body.sage+"',"+
            "type = '"+req.body.type+"',"+
            "department = '"+req.body.department+"',"+
            "sgender = '"+req.body.sgender+"' "+
            "WHERE pid = '"+pid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//查询所有项目
router.get('/queryProject', (req, res) => {
  let sql = "select * from project";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})
//根据ID查询项目
router.get('/queryProjectById', (req, res) => {
  let pid = req.query.pid;
  let sql = "select * from project "+
  "where pid = '"+pid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//分页查询所有项目
router.get('/queryProjectPage', (req, res) => {
  //模糊查询项目名称
  let pname = req.query.pname;
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 9;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select * from project where 1=1 ";
  if(pname!="" && pname!=undefined){
    sql += "and pname like '%"+pname+"%' ";
  }
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = "select * from project where 1=1 ";
    if(pname!="" || pname!=undefined){
      sql2 += "and pname like '%"+pname+"%'";
    }
    sql2 += "limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})


//新增套餐
router.post('/addCombo', (req, res) => {
  let sql = "INSERT INTO `combo`(`cname`,`sage`,`sgender`,`meaning`,`start`,`stop`)  "+ 
  "VALUES ('"+req.body.cname+"', '"+req.body.sage+"', '"+req.body.sgender+"', '"+req.body.meaning+"', '"+req.body.start+"', '"+req.body.stop+"')";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
        res.json({
          code: 200
        })
    }
  })
})
//查询所有套餐
router.get('/queryCombo', (req, res) => {
  let sql = "select * from `combo`;";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})
//分页查询所有套餐
router.get('/queryComboPage', (req, res) => {
  //模糊查询套餐名称
  let input = req.query.input;
    //模糊查询起始结束时间
  let start = req.query.start;
  let stops = req.query.stop;
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 9;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select * from combo where 1=1 ";
            if(input!='' && input!=undefined){
              sql += "and cname like '%"+input+"%' ";
            }
            if(start!='' && start!=undefined || stops!='' && stops!=undefined){
              sql += "and `start` Between '"+start+"' AND '"+stops+"' and `stop` Between '"+start+"' AND '"+stops+"' ";
            }
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = sql+"limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})
// 删除套餐
router.get('/delCombo', (req, res) => {
  let cid = req.query.cid;
  let sql = "DELETE FROM `combo` WHERE cid = '"+cid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//修改套餐
router.post('/updateCombo', (req, res) => {
  //获取套餐编号
  let cid = req.body.cid;
  let sql = "UPDATE `combo` SET "+
            "cname = '"+req.body.cname+"',"+
            "sage = '"+req.body.sage+"',"+
            "sgender = '"+req.body.sgender+"',"+
            "meaning = '"+req.body.meaning+"',"+
            "start = '"+req.body.start+"',"+
            "`stop` = '"+req.body.stop+"' "+
            "WHERE cid = '"+cid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//根据ID查询套餐
router.get('/queryComboById', (req, res) => {
  let cid = req.query.cid;
  let sql = "select * from combo "+
  "where cid = '"+cid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//根据套餐编号查询出他所有的项目
router.get('/queryProjectByCid', (req, res) => {
  let cid = req.query.cid;
  let sql = "select p.pid as pid from project as p "+
  "inner join cp on cp.pid=p.pid  "+
  "where cp.cid = '"+cid+"' ";
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//新增套餐对应项目
router.get('/addcp', (req, res) => {
  let sql = "INSERT INTO `cp`(cid,pid) "+ 
  "VALUES ('"+req.query.cid+"', '"+req.query.pid+"' )";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
        res.json({
          code: 200
        })
    }
  })
})
// 删除套餐对应项目
router.get('/delcp', (req, res) => {
  let cid = req.query.cid;
  let sql = "DELETE FROM `cp` WHERE cid = '"+cid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})

//分页查询所有记录
router.get('/queryRecordPage', (req, res) => {
  //查询记录状态
  let status = req.query.status;
    //模糊查询起始结束时间
  let start = req.query.start;
  let stops = req.query.stop;
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 13;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select r.reid as reid,u.uid as uid,u.`name` as uname,u.phone as phone,u.gender as gender,c.cname as cname,c.`start`,c.`stop`,r.`status` as `status`,r.time as time from record as r "+
            "inner join `user` as u on r.uid=u.uid "+
            "inner join combo as c on r.cid=c.cid where 1=1 ";

            
            if(status!='' && status!=undefined){
              sql += "and r.status = '"+status+"' ";
            }
            if(start!='' && start!=undefined || stops!='' && stops!=undefined){
              sql += "and r.time Between '"+start+"' AND '"+stops+"' ";
            }
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = sql +="limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})
//根据ID查询记录
router.get('/queryRecordById', (req, res) => {
  let reid = req.query.reid;
  let sql = "select r.reid as reid,u.uid as uid,u.`name` as uname,u.phone as phone,u.gender as gender,c.cname as cname,c.`start`,c.`stop`,r.`status` as `status`,r.time as time from record as r "+
            "inner join `user` as u on r.uid=u.uid "+
            "inner join combo as c on r.cid=c.cid "+
            "where r.reid = '"+reid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//修改记录
router.post('/updateRecord', (req, res) => {
  //获取记录编号
  let reid = req.body.reid;
  let sql = "UPDATE `record` SET "+
            "time = '"+req.body.time+"',"+
            "status = '"+req.body.status+"' "+
            "WHERE reid = '"+reid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
// 删除记录
router.get('/delRecord', (req, res) => {
  let reid = req.query.reid;
  let sql = "DELETE FROM `record` WHERE reid = '"+reid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})

//分页查询所有日志
router.get('/queryLoginLogsPage', (req, res) => {
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 13;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select u.uid as uid,u.account as account,l.* from `logs` as l "+
            "inner join `user` as u on l.uid=u.uid"
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = "select u.uid as uid,u.account as account,l.* from `logs` as l "+
               "inner join `user` as u on l.uid=u.uid "+
               "order by l.time desc "+
               "limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})
//新增登入登出登录日志
router.post('/addLoginLogs', (req, res) => {
  //获取操作详情(0:登入,1:登出)
  let details = req.body.details;
  console.log("草拟吗",details)
  if(details=="0"){
    details='登入了系统';
  }else if(details=="1"){
    details='登出了系统';
  }
  let sql = "INSERT INTO `logs`(`uid`,`ip`,`details`,`time`) "+ 
  "VALUES ('"+req.body.uid+"', '"+req.body.ip+"','"+details+"','"+req.body.time+"')";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})

//新增轮播图
router.post('/addRotation', (req, res) => {
  let sql = "INSERT INTO `rotation`(`roname`,`rourl`,`describe`,`status`) "+ 
  "VALUES ('"+req.body.roname+"', '"+req.body.rourl+"','"+req.body.describe+"','"+req.body.status+"' )";

  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息",err)
    } else {
        res.json({
          code: 200
        })
    }
  })
})
// 删除轮播图
router.get('/delRotation', (req, res) => {
  let roid = req.query.roid;
  let sql = "DELETE FROM `rotation` WHERE roid = '"+roid+"'";
  db.query(sql, (err, result) => {
    if (err) {
      console.log(err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//修改轮播图
router.post('/updateRotation', (req, res) => {
  //获取轮播图编号
  let roid = req.body.roid;
  let sql = "UPDATE `rotation` SET "+
            "roname = '"+req.body.roname+"',"+
            "rourl = '"+req.body.rourl+"',"+
            "`describe` = '"+req.body.describe+"',"+
            "status = '"+req.body.status+"' "+
            "WHERE roid = "+roid+" ";
  db.query(sql, (err, result) => {
    if (err) {
      console.log("错误信息1",err)
    } else {
      res.json({
        code: 200
      })
    }
  })
})
//根据ID查询轮播图
router.get('/queryRotationById', (req, res) => {
  let roid = req.query.roid;
  let sql = "select * from rotation "+
  "where roid = '"+roid+"'";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    console.log(results);
    res.send(results)
  })
})
//查询所有轮播图
router.get('/queryRotation', (req, res) => {
  let sql = "select * from `rotation`;";
  db.query({
    sql: sql
  }, (err, results, fields) => {
    res.send(results)
  })
})
//分页查询所有轮播图
router.get('/queryRotationPage', (req, res) => {
  //默认页码
  let currentPage = 1;
  //默认显示数据条数
  let pageSize = 9;
  if(req.query.currentPage){
    currentPage = req.query.currentPage;
  }
  if(req.query.pageSize){
    pageSize = req.query.pageSize;
  }
  //最后一页页码
  let last_page = currentPage-1;
  if(currentPage<=1){
    last_page = 1;
  }
  console.log("当前页",currentPage);
 
  let total = 0;
  //查询总条数
  let sql = "select * from menu";
  console.log(sql);
  db.query({
    sql: sql
  }, (err, results, fields) => {

    console.log(results.length)
    total = results.length;
    currentPage = (currentPage - 1 ) * pageSize;
    let sql2 = "select * from menu "+
               "limit "+currentPage+","+pageSize+" ";
    db.query({
      sql: sql2
    }, (err, results, fields) => {
      console.log(err);
      console.log("这是sql2",sql2)
      if(results!=''){
        results[0].backup3 = total;
      }
      res.send(results)
    })
  })
})




module.exports = router